package com.web.dao;

import com.web.controller.web.SysController;
import com.web.entity.User;
import com.web.entity.UserExample;
import com.web.entity.UserExample.Criteria;
import com.web.entity.UserExample.Criterion;
import org.apache.commons.lang3.StringUtils;

import java.util.List;
import java.util.Map;

import static org.apache.ibatis.jdbc.SqlBuilder.*;
import static org.apache.ibatis.jdbc.SqlBuilder.LEFT_OUTER_JOIN;

/**
 * @author FDjavaone
 */
public class UserSqlProvider {

    public String countByExample(UserExample example) {
        BEGIN();
        SELECT("count(*)");
        FROM("t_user");
        applyWhere(example, false);
        return SQL();
    }

    public String deleteByExample(UserExample example) {
        BEGIN();
        DELETE_FROM("t_user");
        applyWhere(example, false);
        return SQL();
    }

    public String insertSelective(User record) {
        BEGIN();
        INSERT_INTO("t_user");

        if (record.getUsername() != null) {
            VALUES("username", "#{username,jdbcType=VARCHAR}");
        }

        if (record.getPassword() != null) {
            VALUES("password", "#{password,jdbcType=VARCHAR}");
        }

        if (record.getAge() != null) {
            VALUES("age", "#{age,jdbcType=INTEGER}");
        }

        if (record.getGender() != null) {
            VALUES("gender", "#{gender,jdbcType=INTEGER}");
        }

        if (record.getPhone() != null) {
            VALUES("phone", "#{phone,jdbcType=VARCHAR}");
        }

        if (record.getEmail() != null) {
            VALUES("email", "#{email,jdbcType=VARCHAR}");
        }

        if (record.getQq() != null) {
            VALUES("QQ", "#{qq,jdbcType=VARCHAR}");
        }

        if (record.getDescription() != null) {
            VALUES("description", "#{description,jdbcType=VARCHAR}");
        }

        if (record.getStatus() != null) {
            VALUES("status", "#{status,jdbcType=INTEGER}");
        }

        if (record.getAccountNonLocked() != null) {
            VALUES("account_non_Locked", "#{accountNonLocked,jdbcType=BIT}");
        }

        if (record.getCredentialsNonExpired() != null) {
            VALUES("credentials_non_expired", "#{credentialsNonExpired,jdbcType=BIT}");
        }

        if (record.getAccountNonExpired() != null) {
            VALUES("account_non_expired", "#{accountNonExpired,jdbcType=BIT}");
        }

        if (record.getEnabled() != null) {
            VALUES("enabled", "#{enabled,jdbcType=BIT}");
        }

        if (record.getCreateTime() != null) {
            VALUES("create_time", "#{createTime,jdbcType=TIMESTAMP}");
        }

        if (record.getModifyTime() != null) {
            VALUES("modify_time", "#{modifyTime,jdbcType=TIMESTAMP}");
        }

        if (record.getRoleId() != null) {
            VALUES("role_id", "#{roleId,jdbcType=INTEGER}");
        }

        if (record.getLastpasswordresetdate() != null) {
            VALUES("lastPasswordResetDate", "#{lastpasswordresetdate,jdbcType=TIMESTAMP}");
        }

        return SQL();
    }

    public String selectByUser(User record){
        BEGIN();
        SELECT("id");
        SELECT("username");
        SELECT("password");
        SELECT("age");
        SELECT("gender");
        SELECT("phone");
        SELECT("email");
        SELECT("QQ");
        SELECT("description");
        SELECT("status");
        SELECT("account_non_Locked");
        SELECT("credentials_non_expired");
        SELECT("account_non_expired");
        SELECT("enabled");
        SELECT("create_time");
        SELECT("modify_time");
        SELECT("role_id");
        SELECT("lastPasswordResetDate");
        FROM("t_user");

        if (record.getPhone() != null&&!"".equals(record.getPhone())) {
            WHERE("phone = #{phone,jdbcType=VARCHAR}");
        }

        if (record.getUsername() != null&&!"".equals(record.getUsername())) {
            WHERE("username = #{username,jdbcType=VARCHAR}");
        }

        return SQL();
    }



    public String selectByExample(UserExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("id");
        } else {
            SELECT("id");
        }
        SELECT("username");
        SELECT("password");
        SELECT("age");
        SELECT("gender");
        SELECT("phone");
        SELECT("email");
        SELECT("QQ");
        SELECT("description");
        SELECT("status");
        SELECT("account_non_Locked");
        SELECT("credentials_non_expired");
        SELECT("account_non_expired");
        SELECT("enabled");
        SELECT("create_time");
        SELECT("modify_time");
        SELECT("role_id");
        SELECT("lastPasswordResetDate");
        FROM("t_user");
        applyWhere(example, false);

        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }

        return SQL();
    }

    public String selectRoleByExample(UserExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tu.id");
        } else {
            SELECT("tu.id");
        }
        SELECT("tu.username");
        SELECT("tu.password");
        SELECT("tu.age");
        SELECT("tu.gender");
        SELECT("tu.phone");
        SELECT("tu.email");
        SELECT("tu.QQ");
        SELECT("tu.description");
        SELECT("tu.status");
        SELECT("tu.account_non_Locked");
        SELECT("tu.credentials_non_expired");
        SELECT("tu.account_non_expired");
        SELECT("tu.enabled");
        SELECT("tu.create_time");
        SELECT("tu.modify_time");
        SELECT("tu.lastPasswordResetDate");
        SELECT("trr.id as `roles.id`");
        SELECT("trr.text as `roles.text`");
        SELECT("trr.enabled as `roles.enabled`");
        if(example.getPaging()){
            FROM("(select * from t_user " + applyWhere1(example, false, orderBy(example)+" limitSQL ) tu"));
        }else
            FROM("(select * from t_user " + applyWhere1(example, false, orderBy(example)+") tu"));
        LEFT_OUTER_JOIN("t_user_role tur on tu.id=tur.user_id");
        LEFT_OUTER_JOIN("t_role trr on tur.role_id=trr.id");
        return SQL();
    }


    public String selectUserByUser(SysController.UserColumns user) {
        BEGIN();
        SELECT("t_u.* ");
        SELECT("trr.`level` ");
        SELECT("trr.province ");
        SELECT("trr.city ");
        SELECT("trr.region ");
        FROM("(select * from t_user_role where role_id=3 ) t_r ");
        if(user.getUsername()!=null&&!"".equals(user.getUsername())){
            if(user.getPhone()!=null&&!"".equals(user.getPhone())){
                //用户名 手机号都存在
                INNER_JOIN("(select * from t_user where username = #{username,jdbcType=VARCHAR} and phone=#{phone,jdbcType=VARCHAR} ) t_u ON t_u.id=t_r.user_id");
            }else{
                //只有用户名
                INNER_JOIN("(select * from t_user  where username = #{username,jdbcType=VARCHAR}  ) t_u ON t_u.id=t_r.user_id");
            }
        }else{
            if(user.getPhone()!=null&&!"".equals(user.getPhone())){
                //用户名不存在  手机号存在
                INNER_JOIN("(select * from t_user where phone=#{phone,jdbcType=VARCHAR} ) t_u ON t_u.id=t_r.user_id");
            }else{
                //用户名 手机号都不存在
                INNER_JOIN("(select * from t_user) t_u ON t_u.id=t_r.user_id");
            }
        }
        if(user.getLevel()==0){
            LEFT_OUTER_JOIN(" (select * from t_region_record ) trr ON t_u.id=trr.u_id");
        }else if(user.getLevel()==1){
            INNER_JOIN(" (select * from t_region_record where province=#{province,jdbcType=VARCHAR} and level=1 ) trr ON t_u.id=trr.u_id");
        }else if(user.getLevel()==2){
            INNER_JOIN(" (select * from t_region_record where province=#{province,jdbcType=VARCHAR} and city=#{city,jdbcType=VARCHAR} and level=2 ) trr ON t_u.id=trr.u_id");
        }else if(user.getLevel()==3){
            INNER_JOIN(" (select * from t_region_record where province=#{province,jdbcType=VARCHAR} and city=#{city,jdbcType=VARCHAR} and region=#{region,jdbcType=VARCHAR} and level=3 ) trr ON t_u.id=trr.u_id");
        }


        ORDER_BY(" t_u.create_time desc ");
        return SQL();
    }


    public String updateByExampleSelective(Map<String, Object> parameter) {
        User record = (User) parameter.get("record");
        UserExample example = (UserExample) parameter.get("example");

        BEGIN();
        UPDATE("t_user");

        if (record.getId() != null) {
            SET("id = #{record.id,jdbcType=BIGINT}");
        }

        if (record.getUsername() != null) {
            SET("username = #{record.username,jdbcType=VARCHAR}");
        }

        if (record.getPassword() != null) {
            SET("password = #{record.password,jdbcType=VARCHAR}");
        }

        if (record.getAge() != null) {
            SET("age = #{record.age,jdbcType=INTEGER}");
        }

        if (record.getGender() != null) {
            SET("gender = #{record.gender,jdbcType=INTEGER}");
        }

        if (record.getPhone() != null) {
            SET("phone = #{record.phone,jdbcType=VARCHAR}");
        }

        if (record.getEmail() != null) {
            SET("email = #{record.email,jdbcType=VARCHAR}");
        }

        if (record.getQq() != null) {
            SET("QQ = #{record.qq,jdbcType=VARCHAR}");
        }

        if (record.getDescription() != null) {
            SET("description = #{record.description,jdbcType=VARCHAR}");
        }

        if (record.getStatus() != null) {
            SET("status = #{record.status,jdbcType=INTEGER}");
        }

        if (record.getAccountNonLocked() != null) {
            SET("account_non_Locked = #{record.accountNonLocked,jdbcType=BIT}");
        }

        if (record.getCredentialsNonExpired() != null) {
            SET("credentials_non_expired = #{record.credentialsNonExpired,jdbcType=BIT}");
        }

        if (record.getAccountNonExpired() != null) {
            SET("account_non_expired = #{record.accountNonExpired,jdbcType=BIT}");
        }

        if (record.getEnabled() != null) {
            SET("enabled = #{record.enabled,jdbcType=BIT}");
        }

        if (record.getCreateTime() != null) {
            SET("create_time = #{record.createTime,jdbcType=TIMESTAMP}");
        }

        if (record.getModifyTime() != null) {
            SET("modify_time = #{record.modifyTime,jdbcType=TIMESTAMP}");
        }

        if (record.getRoleId() != null) {
            SET("role_id = #{record.roleId,jdbcType=INTEGER}");
        }

        if (record.getLastpasswordresetdate() != null) {
            SET("lastPasswordResetDate = #{record.lastpasswordresetdate,jdbcType=TIMESTAMP}");
        }

        applyWhere(example, true);
        return SQL();
    }

    public String updateByExample(Map<String, Object> parameter) {
        BEGIN();
        UPDATE("t_user");

        SET("id = #{record.id,jdbcType=BIGINT}");
        SET("username = #{record.username,jdbcType=VARCHAR}");
        SET("password = #{record.password,jdbcType=VARCHAR}");
        SET("age = #{record.age,jdbcType=INTEGER}");
        SET("gender = #{record.gender,jdbcType=INTEGER}");
        SET("phone = #{record.phone,jdbcType=VARCHAR}");
        SET("email = #{record.email,jdbcType=VARCHAR}");
        SET("QQ = #{record.qq,jdbcType=VARCHAR}");
        SET("description = #{record.description,jdbcType=VARCHAR}");
        SET("status = #{record.status,jdbcType=INTEGER}");
        SET("account_non_Locked = #{record.accountNonLocked,jdbcType=BIT}");
        SET("credentials_non_expired = #{record.credentialsNonExpired,jdbcType=BIT}");
        SET("account_non_expired = #{record.accountNonExpired,jdbcType=BIT}");
        SET("enabled = #{record.enabled,jdbcType=BIT}");
        SET("create_time = #{record.createTime,jdbcType=TIMESTAMP}");
        SET("modify_time = #{record.modifyTime,jdbcType=TIMESTAMP}");
        SET("role_id = #{record.roleId,jdbcType=INTEGER}");
        SET("lastPasswordResetDate = #{record.lastpasswordresetdate,jdbcType=TIMESTAMP}");

        UserExample example = (UserExample) parameter.get("example");
        applyWhere(example, true);
        return SQL();
    }

    public String updateByPrimaryKeySelective(User record) {
        BEGIN();
        UPDATE("t_user");

        if (record.getUsername() != null) {
            SET("username = #{username,jdbcType=VARCHAR}");
        }

        if (record.getPassword() != null) {
            SET("password = #{password,jdbcType=VARCHAR}");
        }

        if (record.getAge() != null) {
            SET("age = #{age,jdbcType=INTEGER}");
        }

        if (record.getGender() != null) {
            SET("gender = #{gender,jdbcType=INTEGER}");
        }

        if (record.getPhone() != null) {
            SET("phone = #{phone,jdbcType=VARCHAR}");
        }

        if (record.getEmail() != null) {
            SET("email = #{email,jdbcType=VARCHAR}");
        }

        if (record.getQq() != null) {
            SET("QQ = #{qq,jdbcType=VARCHAR}");
        }

        if (record.getDescription() != null) {
            SET("description = #{description,jdbcType=VARCHAR}");
        }

        if (record.getStatus() != null) {
            SET("status = #{status,jdbcType=INTEGER}");
        }

        if (record.getAccountNonLocked() != null) {
            SET("account_non_Locked = #{accountNonLocked,jdbcType=BIT}");
        }

        if (record.getCredentialsNonExpired() != null) {
            SET("credentials_non_expired = #{credentialsNonExpired,jdbcType=BIT}");
        }

        if (record.getAccountNonExpired() != null) {
            SET("account_non_expired = #{accountNonExpired,jdbcType=BIT}");
        }

        if (record.getEnabled() != null) {
            SET("enabled = #{enabled,jdbcType=BIT}");
        }

        if (record.getCreateTime() != null) {
            SET("create_time = #{createTime,jdbcType=TIMESTAMP}");
        }

        if (record.getModifyTime() != null) {
            SET("modify_time = #{modifyTime,jdbcType=TIMESTAMP}");
        }

        if (record.getRoleId() != null) {
            SET("role_id = #{roleId,jdbcType=INTEGER}");
        }

        if (record.getLastpasswordresetdate() != null) {
            SET("lastPasswordResetDate = #{lastpasswordresetdate,jdbcType=TIMESTAMP}");
        }

        WHERE("id = #{id,jdbcType=BIGINT}");

        return SQL();
    }

    protected void applyWhere(UserExample example, boolean includeExamplePhrase) {
        if (example == null) {
            return;
        }

        String parmPhrase1;
        String parmPhrase1_th;
        String parmPhrase2;
        String parmPhrase2_th;
        String parmPhrase3;
        String parmPhrase3_th;
        if (includeExamplePhrase) {
            parmPhrase1 = "%s #{example.oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{example.oredCriteria[%d].allCriteria[%d].value} and #{example.oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{example.oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{example.oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{example.oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        } else {
            parmPhrase1 = "%s #{oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{oredCriteria[%d].allCriteria[%d].value} and #{oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        }

        StringBuilder sb = new StringBuilder();
        List<Criteria> oredCriteria = example.getOredCriteria();
        boolean firstCriteria = true;
        for (int i = 0; i < oredCriteria.size(); i++) {
            Criteria criteria = oredCriteria.get(i);
            if (criteria.isValid()) {
                if (firstCriteria) {
                    firstCriteria = false;
                } else {
                    sb.append(" or ");
                }

                sb.append('(');
                List<Criterion> criterions = criteria.getAllCriteria();
                boolean firstCriterion = true;
                for (int j = 0; j < criterions.size(); j++) {
                    Criterion criterion = criterions.get(j);
                    if (firstCriterion) {
                        firstCriterion = false;
                    } else {
                        sb.append(" and ");
                    }

                    if (criterion.isNoValue()) {
                        sb.append(criterion.getCondition());
                    } else if (criterion.isSingleValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase1, criterion.getCondition(), i, j));
                        } else {
                            sb.append(String.format(parmPhrase1_th, criterion.getCondition(), i, j, criterion.getTypeHandler()));
                        }
                    } else if (criterion.isBetweenValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase2, criterion.getCondition(), i, j, i, j));
                        } else {
                            sb.append(String.format(parmPhrase2_th, criterion.getCondition(), i, j, criterion.getTypeHandler(), i, j, criterion.getTypeHandler()));
                        }
                    } else if (criterion.isListValue()) {
                        sb.append(criterion.getCondition());
                        sb.append(" (");
                        List<?> listItems = (List<?>) criterion.getValue();
                        boolean comma = false;
                        for (int k = 0; k < listItems.size(); k++) {
                            if (comma) {
                                sb.append(", ");
                            } else {
                                comma = true;
                            }
                            if (criterion.getTypeHandler() == null) {
                                sb.append(String.format(parmPhrase3, i, j, k));
                            } else {
                                sb.append(String.format(parmPhrase3_th, i, j, k, criterion.getTypeHandler()));
                            }
                        }
                        sb.append(')');
                    }
                }
                sb.append(')');
            }
        }

        if (sb.length() > 0) {
            WHERE(sb.toString());
        }
    }


    protected String applyWhere1(UserExample example, boolean includeExamplePhrase, String suffix) {
        if (example == null) {
            return "";
        }

        String parmPhrase1;
        String parmPhrase1_th;
        String parmPhrase2;
        String parmPhrase2_th;
        String parmPhrase3;
        String parmPhrase3_th;
        if (includeExamplePhrase) {
            parmPhrase1 = "%s #{example.oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{example.oredCriteria[%d].allCriteria[%d].value} and #{example.oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{example.oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{example.oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{example.oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{example.oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        } else {
            parmPhrase1 = "%s #{oredCriteria[%d].allCriteria[%d].value}";
            parmPhrase1_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s}";
            parmPhrase2 = "%s #{oredCriteria[%d].allCriteria[%d].value} and #{oredCriteria[%d].criteria[%d].secondValue}";
            parmPhrase2_th = "%s #{oredCriteria[%d].allCriteria[%d].value,typeHandler=%s} and #{oredCriteria[%d].criteria[%d].secondValue,typeHandler=%s}";
            parmPhrase3 = "#{oredCriteria[%d].allCriteria[%d].value[%d]}";
            parmPhrase3_th = "#{oredCriteria[%d].allCriteria[%d].value[%d],typeHandler=%s}";
        }

        StringBuilder sb = new StringBuilder();
        List<Criteria> oredCriteria = example.getOredCriteria();
        boolean firstCriteria = true;
        for (int i = 0; i < oredCriteria.size(); i++) {
            Criteria criteria = oredCriteria.get(i);
            if (criteria.isValid()) {
                if (firstCriteria) {
                    firstCriteria = false;
                } else {
                    sb.append(" or ");
                }

                sb.append('(');
                List<Criterion> criterions = criteria.getAllCriteria();
                boolean firstCriterion = true;
                for (int j = 0; j < criterions.size(); j++) {
                    Criterion criterion = criterions.get(j);
                    if (firstCriterion) {
                        firstCriterion = false;
                    } else {
                        sb.append(" and ");
                    }

                    if (criterion.isNoValue()) {
                        sb.append(criterion.getCondition());
                    } else if (criterion.isSingleValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase1, criterion.getCondition(), i, j));
                        } else {
                            sb.append(String.format(parmPhrase1_th, criterion.getCondition(), i, j, criterion.getTypeHandler()));
                        }
                    } else if (criterion.isBetweenValue()) {
                        if (criterion.getTypeHandler() == null) {
                            sb.append(String.format(parmPhrase2, criterion.getCondition(), i, j, i, j));
                        } else {
                            sb.append(String.format(parmPhrase2_th, criterion.getCondition(), i, j, criterion.getTypeHandler(), i, j, criterion.getTypeHandler()));
                        }
                    } else if (criterion.isListValue()) {
                        sb.append(criterion.getCondition());
                        sb.append(" (");
                        List<?> listItems = (List<?>) criterion.getValue();
                        boolean comma = false;
                        for (int k = 0; k < listItems.size(); k++) {
                            if (comma) {
                                sb.append(", ");
                            } else {
                                comma = true;
                            }
                            if (criterion.getTypeHandler() == null) {
                                sb.append(String.format(parmPhrase3, i, j, k));
                            } else {
                                sb.append(String.format(parmPhrase3_th, i, j, k, criterion.getTypeHandler()));
                            }
                        }
                        sb.append(')');
                    }
                }
                sb.append(')');
            }
        }

        if (sb.length() > 0) {
            sb.append(suffix);
            return " where " + sb.toString();
        } else {
            sb.append(suffix);
            return sb.toString();
        }
    }

    public String orderBy(UserExample example){
            if(StringUtils.isNotBlank(example.getOrderByClause()))
                return " order by "+example.getOrderByClause();
            else return "";
    }
}